#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
Excel操作
"""
import os
import random
import string
import datetime
import xlrd
import xlwt
from xlrd import XL_CELL_NUMBER


class Excel(object):
    """
    Excel文件操作
    """
    def __init__(self, home="/tmp"):
        """
        初始化, 临时文件夹
        """
        self.home = home

    def load(self, file_name, sheet_index=0):
        """
        assume the first line is title
        :param file_name: 文件名
        :param sheet_index: Sheet索引号
        :return data:[{
            "titile": "值"
        }, ... ]
        """
        title, data = [], []
        work_book = xlrd.open_workbook(file_name)
        sheet = work_book.sheet_by_index(sheet_index)
        for c in xrange(sheet.ncols):
            title.append(sheet.cell(0, c).value)
        for r in xrange(1, sheet.nrows):
            unit = {}
            for c in xrange(sheet.ncols):
                if sheet.cell(r, c).ctype == XL_CELL_NUMBER:
                    unit[title[c]] = "%f" % sheet.cell(r, c).value
                else:
                    unit[title[c]] = sheet.cell(r, c).value
            data.append(unit)
        return data

    def load_bycont(self, file_cont, sheet_index=0):
        """
        根据文件内容进行解析
        :param str file_cont: 文件内容
        :param int sheet_index: Sheet索引号
        :return data:[{
            "titile": "值"
        }, ... ]
        """
        title, data = [], []
        work_book = xlrd.open_workbook(file_contents=file_cont)
        sheet = work_book.sheet_by_index(sheet_index)
        for c in xrange(sheet.ncols):
            title.append(sheet.cell(0, c).value)
        for r in xrange(1, sheet.nrows):
            unit = {}
            for c in xrange(sheet.ncols):
                if sheet.cell(r, c).ctype == XL_CELL_NUMBER:
                    unit[title[c]] = "%f" % sheet.cell(r, c).value
                else:
                    unit[title[c]] = sheet.cell(r, c).value
            data.append(unit)
        return data

    def load_byindex(self, file_name, sheet_index=0):
        """
        加载excel文件
        :param file_name: 文件名称
        :param sheet_index: 页脚编号
        :return data:[{
            "列编号": "值"
        }, ... ]
        """
        data = []
        work_book = xlrd.open_workbook(file_name)
        sheet = work_book.sheet_by_index(sheet_index)
        for r in xrange(1, sheet.nrows):
            unit = {}
            for c in xrange(sheet.ncols):
                unit[c] = sheet.cell(r, c).value
            data.append(unit)
        return data

    def generate(self, title, data, sep=1, callback=None, date_format="%Y-%m-%d"):
        """
        生成Excel
        :param title: excel title
        :param data: data result，list
        :param date_format: 可选 自定义时间格式
        sep: width ratio
        """
        work_book = xlwt.Workbook("UTF-8")
        sheet = work_book.add_sheet("sheet", True)
        ncols = len(title)

        title_style = self.get_title_style()
        for j in xrange(ncols):
            sheet.col(j).width = int(3333 * sep)
            sheet.write(0, j, title[j][1], title_style)
        nrows = len(data)
        text_style = self.get_text_style()
        for i in xrange(nrows):
            for j in xrange(ncols):
                key = title[j][0]
                val = callback(data[i], key) if callback else data[i].get(key, "")
                if isinstance(val, datetime.datetime):
                    val = val.strftime(date_format)
                sheet.write(i + 1, j, val, text_style)
        filename = self.tmp_filename()
        work_book.save(filename)
        content = ""
        with open(filename, "rb") as f:
            content = f.read()
        os.remove(filename)
        return content

    def generate_custom(self, title, data, sep=1, callback=None):
        """
        generate excel content
        title: excel title
        data: data result，list
        sep: width ratio
        """
        work_book = xlwt.Workbook('UTF-8')
        sheet = work_book.add_sheet('sheet', True)
        ncols = len(title)
        # write title
        title_style = self.get_title_style()
        for j in xrange(ncols):
            sheet.col(j).width = int(3333 * sep)
            sheet.write(0, j, title[j], title_style)
        nrows = len(data)
        text_style = self.get_text_style()
        for i in xrange(nrows):
            for j in xrange(ncols):
                val = data[i][j]
                if isinstance(val, datetime.datetime):
                    val = val.strftime('%Y-%m-%d')
                sheet.write(i + 1, j, val, text_style)
        filename = self.tmp_filename()
        work_book.save(filename)
        content = ''
        with open(filename, 'rb') as f:
            content = f.read()
        os.remove(filename)
        return content

    def tmp_filename(self):
        """
        临时文件名
        :return: 返回临时文件名
        """
        fname = "".join(random.sample(string.lowercase, 10))
        return os.path.join(self.home, fname)

    def get_title_style(self):
        """
        获取title的Style
        """
        style = xlwt.XFStyle()
        font = xlwt.Font()
        font.bold = True
        style.font = font
        alignment = xlwt.Alignment()
        alignment.horz = xlwt.Alignment.HORZ_CENTER
        alignment.vert = xlwt.Alignment.VERT_CENTER
        style.alignment = alignment
        return style

    def get_text_style(self):
        """
        获取text的Style
        """
        style = xlwt.XFStyle()
        alignment = xlwt.Alignment()
        alignment.horz = xlwt.Alignment.HORZ_CENTER
        alignment.vert = xlwt.Alignment.VERT_CENTER
        style.alignment = alignment
        return style

if __name__ == "__main__":
    TITLE = [("name", "姓名"), ("age", "年龄"), ("gender", "性别")]
    DATA = [
        {"name": "liangsix", "age": 10, "gender": "男"},
        {"name": "梁六", "age": 12, "gender": "男"}
    ]
    FP = open("test.xls", "wb")
    FP.write(Excel().generate(TITLE, DATA))
    FP.close()

